﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProjetoJornada_DAL;
using Basicas;
using MySql.Data.MySqlClient;
using Basicas.Utils;
using System.Data;

namespace ProjetoJornada_DAL.Repositorio
{
    public class RepMatricula
    {
        private DAO dao;

        public RepMatricula()
        {
            this.dao = DAO.Instance;
        }

        #region Metodos Publicos
        public Int32 Inserir(Matricula matricula)
        {
            String storeProcedure = "sp_Matricula_Inserir";
            Int32 idMatricula = 0;

            try
            {
                MySqlParameter[] parametros = new MySqlParameter[8];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"IdTurmaIn";
                parametros[0].Value = matricula.Turma.Id;
                parametros[0].DbType = DbType.Int32;

                parametros[1] = new MySqlParameter();
                parametros[1].ParameterName = @"IdDescontoIn";
                parametros[1].Value = matricula.Desconto.Id;
                parametros[1].DbType = DbType.Int32;

                parametros[2] = new MySqlParameter();
                parametros[2].ParameterName = @"IdAlunoIn";
                parametros[2].Value = matricula.Aluno.Cpf;
                parametros[2].DbType = DbType.String;

                parametros[3] = new MySqlParameter();
                parametros[3].ParameterName = @"FormaDePagamentoIn";
                parametros[3].Value = matricula.FormaPagamento.Id;
                parametros[3].DbType = DbType.Int32;

                parametros[4] = new MySqlParameter();
                parametros[4].ParameterName = @"StatusPagamentoIn";
                parametros[4].Value = matricula.StatusPagamento;
                parametros[4].DbType = DbType.Boolean;

                parametros[5] = new MySqlParameter();
                parametros[5].ParameterName = @"ValorPagoIn";
                parametros[5].Value = matricula.ValorPago;
                parametros[5].DbType = DbType.Double;

                parametros[6] = new MySqlParameter();
                parametros[6].ParameterName = @"DataMatriculaIn";
                parametros[6].Value = matricula.DataMatricula;
                parametros[6].DbType = DbType.Date;

                parametros[7] = new MySqlParameter();
                parametros[7].ParameterName = @"Result";
                parametros[7].DbType = DbType.Int32;
                parametros[7].Direction = ParameterDirection.Output;

                idMatricula = (Int32)dao.ExecutaFuncao(parametros, storeProcedure);
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "Inserir Matricula - Repositorio");
                throw ex;
            }
            return idMatricula;
        }
        public bool Editar(Matricula matricula)
        {
            String storeProcedure = "sp_Matricula_Edit";

            try
            {
                MySqlParameter[] parametros = new MySqlParameter[8];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"IdIn";
                parametros[0].Value = matricula.Id;
                parametros[0].DbType = DbType.Int32;

                parametros[1] = new MySqlParameter();
                parametros[1].ParameterName = @"IdTurmaIn";
                parametros[1].Value = matricula.Turma.Id;
                parametros[1].DbType = DbType.Int32;

                parametros[2] = new MySqlParameter();
                parametros[2].ParameterName = @"IdDescontoIn";
                parametros[2].Value = matricula.Desconto.Id;
                parametros[2].DbType = DbType.Int32;

                parametros[3] = new MySqlParameter();
                parametros[3].ParameterName = @"IdAlunoIn";
                parametros[3].Value = matricula.Aluno.Cpf;
                parametros[3].DbType = DbType.String;

                parametros[4] = new MySqlParameter();
                parametros[4].ParameterName = @"FormaDePagamentoIn";
                parametros[4].Value = matricula.FormaPagamento.Id;
                parametros[4].DbType = DbType.Int32;

                parametros[5] = new MySqlParameter();
                parametros[5].ParameterName = @"StatusPagamentoIn";
                parametros[5].Value = matricula.StatusPagamento;
                parametros[5].DbType = DbType.Boolean;

                parametros[6] = new MySqlParameter();
                parametros[6].ParameterName = @"ValorPagoIn";
                parametros[6].Value = matricula.ValorPago;
                parametros[6].DbType = DbType.Double;

                parametros[7] = new MySqlParameter();
                parametros[7].ParameterName = @"DataMatriculaIn";
                parametros[7].Value = matricula.DataMatricula;
                parametros[7].DbType = DbType.Date;

                return dao.ExecutaSql(parametros, storeProcedure);
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "Editar Matricula - Repositorio");
                throw ex;
            }
        }
        public bool Remover(Matricula matricula)
        {
            String storedProcedure = "sp_Matricula_Delete";

            try
            {
                MySqlParameter[] parametros = new MySqlParameter[1];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"IdIn";
                parametros[0].Value = matricula.Id;
                parametros[0].DbType = DbType.Int32;

                return dao.ExecutaSql(parametros, storedProcedure);
            }
            catch (Exception ex)
            { 
                Auxiliar.GravaLog(ex.Message, "Remover Matricula - Repositorio");
                throw ex;
            }
        }

        public List<Matricula> BuscarPorTurma(int turmaId)
        {
            String storeProcedure = "sp_Matricula_Select_Turma";
            MySqlDataReader dr = null;
            List<Matricula> matriculas = new List<Matricula>();

            try
            {
                MySqlParameter[] parametros = new MySqlParameter[1];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"turmaId";
                parametros[0].Value = turmaId;
                parametros[0].DbType = System.Data.DbType.Int32;

                dr = dao.ExecutaLeituraProcedure(parametros, storeProcedure);

                while (dr.Read())
                {
                    Matricula matricula = new Matricula();
                    Aluno aluno = new Aluno();
                    aluno.Cpf = Convert.ToString(dr["Id_Aluno"]);
                    matricula.Aluno = aluno;
                    matricula.Id = Convert.ToInt32(dr["Id_Matricula"]);
                    matricula.DataMatricula = Convert.ToDateTime(dr["DataMatricula"]);
                    matricula.ValorPago = Convert.ToDouble(dr["ValorPago"]);
                    matricula.StatusPagamento = Convert.ToBoolean(dr["StatusPagamento"]);
                    matriculas.Add(matricula);
                }

                dr.Close();
                dao.FecharConexao();

                foreach (Matricula mat in matriculas)
                {
                    RepAluno repAluno = new RepAluno();
                    Aluno aluno = repAluno.ConsultarAlunoPorCpf(mat.Aluno.Cpf);
                    mat.Aluno = aluno;
                }

                return matriculas;
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "ConsultarTurmaPorId - Repositorio");
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }

                dao.FecharConexao();
            }

        }

        public Matricula BuscarPorId(int matriculaId)
        {
            String storeProcedure = "sp_Matricula_Select_Id";
            MySqlDataReader dr = null;
            Matricula matricula = null;

            try
            {
                MySqlParameter[] parametros = new MySqlParameter[1];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"matriculaId";
                parametros[0].Value = matriculaId;
                parametros[0].DbType = System.Data.DbType.Int32;

                dr = dao.ExecutaLeituraProcedure(parametros, storeProcedure);

                if(dr.Read())
                {
                    matricula = new Matricula();
                    Aluno aluno = new Aluno();
                    aluno.Cpf = Convert.ToString(dr["Id_Aluno"]);
                    matricula.Aluno = aluno;
                    matricula.Id = Convert.ToInt32(dr["Id_Matricula"]);
                    matricula.DataMatricula = Convert.ToDateTime(dr["DataMatricula"]);
                    matricula.StatusPagamento = Convert.ToBoolean(dr["StatusPagamento"]);
                    matricula.Turma = new Turma();
                    matricula.Turma.Id = Convert.ToInt32(dr["Id_Turma"]);
                    matricula.ValorPago = Convert.ToDouble(dr["ValorPago"]);

                    if (dr["Id_Desconto"] != DBNull.Value)
                    {
                        Desconto desconto = new Desconto();
                        desconto.Id = Convert.ToInt32(dr["Id_Desconto"]);
                        matricula.Desconto = desconto;
                    }
                }

                dr.Close();
                dao.FecharConexao();

                if (matricula.Desconto != null)
                {
                    RepDesconto repDesconto = new RepDesconto();
                    matricula.Desconto = repDesconto.ConsultarDescontoPorId(Convert.ToInt32(matricula.Desconto.Id));
                }
             
                return matricula;
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "ConsultarTurmaPorId - Repositorio");
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }

                dao.FecharConexao();
            }

        }

        public List<Matricula> BuscarPorCpf(string cpf)
        {
            String storeProcedure = "sp_Matricula_Select_Cpf";
            MySqlDataReader dr = null;
        
            try
            {
                MySqlParameter[] parametros = new MySqlParameter[1];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"alunoCpf";
                parametros[0].Value = cpf;
                parametros[0].DbType = System.Data.DbType.String;

                dr = dao.ExecutaLeituraProcedure(parametros, storeProcedure);

                List<Matricula> matriculas = new List<Matricula>();

                while (dr.Read())
                {
                    Matricula matricula = new Matricula();
                    Aluno aluno = new Aluno();
                    aluno.Cpf = Convert.ToString(dr["Id_Aluno"]);
                    matricula.Aluno = aluno;
                    matricula.Id = Convert.ToInt32(dr["Id_Matricula"]);
                    matricula.DataMatricula = Convert.ToDateTime(dr["DataMatricula"]);
                    matricula.StatusPagamento = Convert.ToBoolean(dr["StatusPagamento"]);
                    matricula.Turma = new Turma();
                    matricula.Turma.Id = Convert.ToInt32(dr["Id_Turma"]);
                    matricula.ValorPago = Convert.ToDouble(dr["ValorPago"]);

                    if (dr["Id_Desconto"] != DBNull.Value)
                    {
                        Desconto desconto = new Desconto();
                        desconto.Id = Convert.ToInt32(dr["Id_Desconto"]);
                        matricula.Desconto = desconto;
                    }

                    matriculas.Add(matricula);
                }

                dr.Close();
                dao.FecharConexao();

                foreach (Matricula mat in matriculas)
                {
                    if (mat.Desconto != null)
                    {
                        RepDesconto repDesconto = new RepDesconto();
                        mat.Desconto = repDesconto.ConsultarDescontoPorId(Convert.ToInt32(mat.Desconto.Id));
                    }

                    if (mat.Turma != null)
                    {
                        RepTurma repTurma = new RepTurma();
                        mat.Turma = repTurma.Consultar(mat.Turma.Id);
                    }

                    if (mat.Aluno != null)
                    {
                        RepAluno repAluno = new RepAluno();
                        mat.Aluno = repAluno.ConsultarAlunoPorCpf(mat.Aluno.Cpf);
                    }
                }

                return matriculas;
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "ConsultarTurmaPorId - Repositorio");
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }

                dao.FecharConexao();
            }

        }

        public List<Matricula> ConsultarMatriculaPorCPFNaJornada(string cpf, int idJornada)
        {
            String storeProcedure = "sp_Matricula_Select_Cpf_Jornada";
            MySqlDataReader dr = null;

            try
            {
                MySqlParameter[] parametros = new MySqlParameter[2];

                parametros[0] = new MySqlParameter();
                parametros[0].ParameterName = @"alunoCpf";
                parametros[0].Value = cpf;
                parametros[0].DbType = System.Data.DbType.String;

                parametros[1] = new MySqlParameter();
                parametros[1].ParameterName = @"idJornada";
                parametros[1].Value = idJornada;
                parametros[1].DbType = System.Data.DbType.Int32;

                dr = dao.ExecutaLeituraProcedure(parametros, storeProcedure);

                List<Matricula> matriculas = new List<Matricula>();

                while (dr.Read())
                {
                    Matricula matricula = new Matricula();
                    Aluno aluno = new Aluno();
                    aluno.Cpf = Convert.ToString(dr["Id_Aluno"]);
                    matricula.Aluno = aluno;
                    matricula.Id = Convert.ToInt32(dr["Id_Matricula"]);
                    matricula.DataMatricula = Convert.ToDateTime(dr["DataMatricula"]);
                    matricula.StatusPagamento = Convert.ToBoolean(dr["StatusPagamento"]);
                    matricula.Turma = new Turma();
                    matricula.Turma.Id = Convert.ToInt32(dr["Id_Turma"]);
                    matricula.ValorPago = Convert.ToDouble(dr["ValorPago"]);

                    if (dr["Id_Desconto"] != DBNull.Value)
                    {
                        Desconto desconto = new Desconto();
                        desconto.Id = Convert.ToInt32(dr["Id_Desconto"]);
                        matricula.Desconto = desconto;
                    }

                    matriculas.Add(matricula);
                }

                dr.Close();
                dao.FecharConexao();

                foreach (Matricula mat in matriculas)
                {
                    if (mat.Desconto != null)
                    {
                        RepDesconto repDesconto = new RepDesconto();
                        mat.Desconto = repDesconto.ConsultarDescontoPorId(Convert.ToInt32(mat.Desconto.Id));
                    }

                    if (mat.Turma != null)
                    {
                        RepTurma repTurma = new RepTurma();
                        mat.Turma = repTurma.Consultar(mat.Turma.Id);
                    }

                    if (mat.Aluno != null)
                    {
                        RepAluno repAluno = new RepAluno();
                        mat.Aluno = repAluno.ConsultarAlunoPorCpf(mat.Aluno.Cpf);
                    }
                }

                return matriculas;
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "ConsultarTurmaPorId - Repositorio");
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }

                dao.FecharConexao();
            }

        }
        #endregion
    }
}
